package cn.vastbase.jdbc.advanced.bigdata;

import org.junit.Test;
import org.postgresql.largeobject.LargeObject;
import org.postgresql.largeobject.LargeObjectManager;

import java.io.*;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.*;

/**
 * <p> 大对象类型示例 </p>
 * <p>可用于Vastbase G100 2.2 Build 15.4 Oracle、MySQL兼容模式 </p>
 */
public class LargeObjTestCase {
    /* 定义连接对象 */
    static Connection conn = null;
    static String cname = "org.postgresql.Driver";
    static String url = "jdbc:postgresql://172.16.55.175:5432/zxsdb?loggerLevel=OFF";
    static String username = "zxs";
    static String passwd = "Vastdata#0730";
    static CallableStatement cs = null;
    static Statement stmt = null;
    static PreparedStatement ps = null;
    /* 定义结果集对象 */
    static ResultSet rs = null;

    static Reader reader = null;
    static Writer writer = null;

    static InputStream is = null;
    static OutputStream os = null;


    /**
     * CREATE TABLE test_text (id int, msg text);
     */
    @Test
    public void testText() {
        if (conn() == null) {
            return;
        }
        try {
            ps = conn.prepareStatement("delete from test_text");
            ps.execute();

            // 读取文件文本并写入text字段
            reader = new InputStreamReader(Files.newInputStream(new File("src/main/resources/infile.txt").toPath()));
            ps = conn.prepareStatement("INSERT INTO test_text VALUES (?, ?)");
            ps.setInt(1, 1);
            ps.setCharacterStream(2, reader);
            ps.executeUpdate();
            reader.close();
            System.out.println("读取并写入数据库完成");

            // 读取数据库text字段值，保存到文件
            ps = conn.prepareStatement("SELECT msg FROM test_text WHERE id= ?");
            ps.setInt(1, 1);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                reader = rs.getCharacterStream(1);
                writer = new OutputStreamWriter(Files.newOutputStream(new File("src/main/resources/outfile.txt").toPath()));
                char[] c = new char[1024];
                int i;
                while ((i = reader.read(c)) != -1) {
                    writer.write(c, 0, i);
                }
            }
            System.out.println("读取并保存文本到文件完成");
        } catch (Exception e) {
            System.out.println("[FAIL]  fail." + e.getMessage());
        } finally {
            close();
        }
    }


    /**
     * CREATE TABLE test_clob(uid integer, clob_text clob);
     */
    @Test
    public void testClob() {
        if (conn() == null) {
            return;
        }
        try {
            ps = conn.prepareStatement("delete from test_clob");
            ps.execute();

            ps = conn.prepareStatement("INSERT INTO test_clob VALUES (?, ?)");
            ps.setInt(1, 1);
            ps.setString(2, "温故而知新，可以为师矣");
            ps.executeUpdate();
            System.out.println("读取并写入数据库完成");

            // 读取数据库text字段值，保存到文本
            ps = conn.prepareStatement("SELECT clob_text FROM test_clob WHERE uid= ?");
            ps.setInt(1, 1);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getString(1));
            }
            System.out.println("读取并保存文本到文件完成");
        } catch (Exception e) {
            System.out.println("[FAIL]  fail." + e.getMessage());
        } finally {
            close();
        }

    }

    /**
     * CREATE TABLE test_bytea (id integer, img bytea);
     * 使用setBinaryStream，getBinaryStream存取
     */
    @Test
    public void testBytea() {
        if (conn() == null) {
            return;
        }
        // 读取图片存入数据库
        File file = new File("src/main/resources/inimage.png");
        try {

            ps = conn.prepareStatement("delete from test_bytea");
            ps.execute();

            InputStream is = Files.newInputStream(file.toPath());
            byte[] bytes = new byte[(int) file.length()];
            is.read(bytes);
            ps = conn.prepareStatement("INSERT INTO test_bytea VALUES (?, ?)");
            ps.setInt(1, 1);
            ps.setBytes(2, bytes);
            ps.executeUpdate();

            // 读取数据库，存为图片
            ps = conn.prepareStatement("SELECT img FROM test_bytea WHERE id= ?");
            ps.setInt(1, 1);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                byte[] imgBytes = rs.getBytes(1);
                os = Files.newOutputStream(Paths.get("src/main/resources/outimage.png"));
                os.write(imgBytes);
                os.flush();
            }
        } catch (Exception e) {
            System.out.println("[FAIL]  fail." + e.getMessage());
        } finally {
            close();
        }
    }

    /**
     * CREATE TABLE test_blob (id integer, img blob);
     * 使用setBinaryStream，getBinaryStream存取
     */
    @Test
    public void testBlob() {
        if (conn() == null) {
            return;
        }
        // 读取图片存入数据库
        File file = new File("src/main/resources/inimage.png");
        try {
            ps = conn.prepareStatement("delete from test_blob");
            ps.execute();

            InputStream is = Files.newInputStream(file.toPath());
            ps = conn.prepareStatement("INSERT INTO test_blob VALUES (?, ?)");
            ps.setInt(1, 1);
            ps.setBinaryStream(2, is);
            ps.executeUpdate();
            is.close();

            // 读取数据库，存为图片
            ps = conn.prepareStatement("SELECT img FROM test_blob WHERE id= ?");
            ps.setInt(1, 1);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                is = rs.getBinaryStream(1);
                byte[] data = new byte[is.available()];
                is.read(data);
                os = Files.newOutputStream(Paths.get("src/main/resources/outimage.png"));
                os.write(data);
                os.flush();
            }
        } catch (Exception e) {
            System.out.println("[FAIL]  fail." + e.getMessage());
        } finally {
            close();
        }
    }


    /**
     * 使用setBlob，getBlob存取
     */
    @Test
    public void testBlob2() {
        if (conn() == null) {
            return;
        }
        // 读取图片存入数据库
        try {
            ps = conn.prepareStatement("delete from test_blob");
            ps.execute();

            InputStream is = Files.newInputStream(new File("src/main/resources/inimage.png").toPath());
            ps = conn.prepareStatement("INSERT INTO test_blob VALUES (?, ?)");
            ps.setInt(1, 1);
            ps.setBlob(2, is);
            ps.executeUpdate();
            is.close();

            // 读取数据库，存为图片
            ps = conn.prepareStatement("SELECT img FROM test_blob WHERE id= ?");
            ps.setInt(1, 1);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                is = rs.getBlob(1).getBinaryStream();
                byte[] data = new byte[is.available()];
                is.read(data);

                File file = new File("src/main/resources/outimage.png");
                file.delete();
                os = Files.newOutputStream(file.toPath());
                os.write(data);
                os.flush();
            }
        } catch (Exception e) {
            System.out.println("[FAIL]  fail." + e.getMessage());
        } finally {
            close();
        }
    }


    /**
     * CREATE TABLE imageslo (imgname text, imgoid oid);
     * 使用大对象API写
     */
    @Test
    public void testLargeObjectWrite() {
        if (conn() == null) {
            return;
        }
        try {
            // 大对象操作必须在一个事务中
            conn.setAutoCommit(false);
            // 获取一个大对象管理器
            LargeObjectManager lobj = conn.unwrap(org.postgresql.PGConnection.class).getLargeObjectAPI();
            //创建一个大对象，并授予可读写权限
            long oid = lobj.createLO(LargeObjectManager.READ | LargeObjectManager.WRITE);
            // 开启大对象
            LargeObject obj = lobj.open(oid, LargeObjectManager.WRITE);
            // 文件流
            File file = new File("src/main/resources/inimage.png");
            is = Files.newInputStream(file.toPath());
            // 复制文件内容到大对象
            byte[] data = new byte[2048];
            int s, tl = 0;
            while ((s = is.read(data, 0, 2048)) > 0) {
                obj.write(data, 0, s);
                tl += s;
            }
            // 关闭大对象
            obj.close();

            // 插入大对象的oid到表
            ps = conn.prepareStatement("INSERT INTO imageslo VALUES (?, ?)");
            ps.setString(1, file.getName());
            ps.setLong(2, oid);
            ps.executeUpdate();
            // 提交事务
            conn.commit();
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            close();
        }
    }

    /**
     * 使用大对象API读
     */
    @Test
    public void testLargeObjectRead() {
        if (conn() == null) {
            return;
        }
        try {
            // 插入大对象的oid到表
            ps = conn.prepareStatement("select imgoid from imageslo where imgname=?");
            ps.setString(1, "inimage.png");
            rs = ps.executeQuery();
            rs.next();
            long oid = rs.getLong(1);

            conn.setAutoCommit(false);
            LargeObjectManager lobj = conn.unwrap(org.postgresql.PGConnection.class).getLargeObjectAPI();
            LargeObject obj = lobj.open(oid, LargeObjectManager.READ);

            File file = new File("src/main/resources/outimage.png");
            file.delete();
            os = Files.newOutputStream(file.toPath());
            byte[] data = new byte[2048];
            int s;
            while ((s = obj.read(data, 0, 2048)) > 0) {
                os.write(data, 0, s);
            }
            // 关闭大对象
            obj.close();
            conn.commit();
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            close();
        }
    }


    public Connection conn() {
        try {
            Class.forName(cname);
            return conn = DriverManager.getConnection(url, username, passwd);
        } catch (ClassNotFoundException | SQLException e) {
            System.out.println("[FAIL] conn database fail." + e.getMessage());
        }
        return null;
    }

    public void close() {
        try {
            if (rs != null)
                rs.close();
        } catch (SQLException e) {
            System.out.println("[FAIL] close resultSet fail." + e.getMessage());
        }
        try {
            if (cs != null)
                cs.close();
        } catch (SQLException e) {
            System.out.println("[FAIL] close callableStatement fail." + e.getMessage());
        }
        try {
            if (stmt != null)
                stmt.close();
        } catch (SQLException e) {
            System.out.println("[FAIL] close statement fail." + e.getMessage());
        }
        try {
            if (ps != null)
                ps.close();
        } catch (SQLException e) {
            System.out.println("[FAIL] close preparedStatement fail." + e.getMessage());
        }
        try {
            if (conn != null)
                conn.close();
        } catch (SQLException e) {
            System.out.println("[FAIL] close connection fail." + e.getMessage());
        }
        try {
            if (writer != null)
                writer.close();
        } catch (IOException e) {
            System.out.println("[FAIL] close writer fail." + e.getMessage());
        }
        try {
            if (reader != null)
                reader.close();
        } catch (IOException e) {
            System.out.println("[FAIL] close reader fail." + e.getMessage());
        }
        try {
            if (os != null)
                os.close();
        } catch (IOException e) {
            System.out.println("[FAIL] close outputStream fail." + e.getMessage());
        }
        try {
            if (is != null)
                is.close();
        } catch (IOException e) {
            System.out.println("[FAIL] close inputStream fail." + e.getMessage());
        }

    }

}
